package com.b2c.repository;

import java.util.ArrayList;
import java.util.List;

import com.b2c.entity.tao.TrafficTaoModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.shop.ShopTrafficEntity;
import com.b2c.entity.shop.ShopTrafficGoodsEntity;

@Repository
public class ShopTrafficGoodsRepositroy {
    @Autowired
    JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    public PagingResponse<ShopTrafficGoodsEntity> getList(Integer pageIndex,Integer pageSize ,Integer shopType,Integer shopId,String goodsNum,String date){
        StringBuffer sb = new StringBuffer();

        sb.append("SELECT stg.*,g.goodsName,g.thumbUrl as goodsImg FROM  dc_shop_traffic_goods stg ");
        sb.append(" LEFT JOIN dc_shop_goods g on g.`goodsId` = stg.goodsId ");

        sb.append( " WHERE 1=1 ");


        List<Object> params = new ArrayList<>();
        if (shopType!=null && shopType>0) {
            sb.append(" and stg.shopType = ? ");
            params.add(shopType);
        }
        if (shopId!=null && shopId>0) {
            sb.append(" and stg.shopId = ? ");
            params.add(shopId);
        }
        if(StringUtils.hasText(goodsNum)){
            sb.append(" and (stg.goodsNumber=? OR stg.goodsId=?) ");
            params.add(goodsNum);
            params.add(goodsNum);
        }
        if(StringUtils.hasText(date)){
            sb.append(" and stg.`date`=? ");
            params.add(date);

        }

        sb.append(" ORDER BY stg.`date` DESC,stg.`visits` desc LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<ShopTrafficGoodsEntity> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ShopTrafficGoodsEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    /**
     * 添加
     * @param entity
     * @return
     */
    public ResultVo<Integer> addGoodsTraffic(ShopTrafficGoodsEntity entity){
        //查询店铺
        var shopType = jdbcTemplate.queryForObject("SELECT `type` FROM dc_shop WHERE id=?",Integer.class,entity.getShopId());
        String goodsNumber = "";

        //查询商品信息
        try {
            goodsNumber = jdbcTemplate.queryForObject("SELECT goodsNum from dc_shop_goods where goodsId=?",String.class, entity.getGoodsId());
        } catch (Exception e) {
        }
        

        String sql = "INSERT INTO dc_shop_traffic_goods (shopId,shopType,goodsId,goodsNumber,shows,paidShows,visits,paidVisits,views,collects,"+
                    "paidCollects,orders,paidOrders,carts,paidCarts,chats,paidChats,date,remark,orderUsers,orderAmount,CVR,cvrTag)"+
                    " value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,entity.getShopId(),entity.getShopType(),entity.getGoodsId(),goodsNumber,
        entity.getShows(),entity.getPaidShows(),entity.getVisits(),entity.getPaidVisits(),entity.getViews(),
        entity.getCollects(),entity.getPaidCollects(),entity.getOrders(),entity.getPaidOrders(),entity.getCarts(),entity.getPaidCarts(),
        entity.getChats(),entity.getPaidChats(),entity.getDate(),entity.getRemark(),entity.getOrderUsers(),entity.getOrderAmount(),entity.getCVR(),entity.getCvrTag());
        
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ResultVo<String> addGoodsTrafficTao(List<TrafficTaoModel> list, Integer shopId) {

        if (list == null || list.size() == 0)
            return new ResultVo<>(EnumResultVo.DataError, "参数错误：缺少list", "");

        //查询店铺
        var shopType = jdbcTemplate.queryForObject("SELECT `type` FROM dc_shop WHERE id=?",Integer.class,shopId);

        /******* 插入数据 ********/
        int totalInsert = 0;// 新增数量
        int totalExist = 0;// 已存在数量
        int totalError = 0;// 错误数量

        for (var taoModel : list) {
            try {
                String goodsNumber = "";
                //查询商品信息
                try {
                    goodsNumber = jdbcTemplate.queryForObject("SELECT goodsNum from dc_shop_goods where goodsId=?", String.class, taoModel.getGoodsId());
                } catch (Exception e) {
                }

                String sql = "INSERT INTO dc_shop_traffic_goods (shopId,shopType,goodsId,goodsNumber,visits,collects,orderUsers,orderAmount,carts,`date`)" +
                        " value(?,?,?,?,?,?,?,?,?,?)";

                jdbcTemplate.update(sql, shopId, shopType, taoModel.getGoodsId(), goodsNumber,
                        taoModel.getViews().intValue()
                        , taoModel.getColls().intValue()
                        , taoModel.getBuyers().intValue()
                        , taoModel.getPayAmount()
                        , taoModel.getCarts().intValue()
                        , taoModel.getDate());
                totalInsert++;// 新增成功
            } catch (Exception e1) {
                totalError++;
//            log.error("Fee新增错误,系统异常：" + e.getMessage() + item.getDatetime());

                return new ResultVo<>(EnumResultVo.SystemException, "系统异常：" + e1.getMessage(), "");
            }

        }

        String msg = "Fee新增成功：" + totalInsert + "，失败：" + totalError + "，已存在：" + totalExist;
        //// TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS", msg);
//        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }


    @Transactional
    public PagingResponse<ShopTrafficEntity> getShopTrafficList(Integer pageIndex,Integer pageSize ,Integer shopType,Integer shopId,String date){
        StringBuffer sb = new StringBuffer();

        sb.append("SELECT SQL_CALC_FOUND_ROWS stg.*,(SELECT COUNT(0) FROM dc_shop_traffic_goods WHERE shopId=stg.shopId and `date`= stg.`date`) as goodsCount FROM  dc_shop_traffic stg ");
       
        sb.append( " WHERE 1=1 ");


        List<Object> params = new ArrayList<>();
        if (shopType!=null && shopType>0) {
            sb.append(" and stg.shopType = ? ");
            params.add(shopType);
        }
        if (shopId!=null && shopId>0) {
            sb.append(" and stg.shopId = ? ");
            params.add(shopId);
        }

        if(StringUtils.hasText(date)){
            sb.append(" and stg.`date`=? ");
            params.add(date);

        }

        sb.append(" ORDER BY stg.`date` DESC,stg.`visits` desc LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<ShopTrafficEntity> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ShopTrafficEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    public ResultVo<Integer> addShopTrafficForPdd(ShopTrafficEntity entity) {
        StringBuilder sql = new StringBuilder("INSERT INTO dc_shop_traffic ");
        sql.append("(");
        sql.append("shopId,shopType,visits,views,orderUsers,orderLKR,orders,orderAmount,goods,collects,");
        sql.append("CVR,ATV,UVV,SH,SS,ZBSC,ZBRJGK,ZBViews,ZBOrders,ZBOrderAmount,ZBCVR");
        sql.append(",FSNew,FSOrders,FSROrder,FSOrderAmount,FSFGL");
        sql.append(",showsQZ,visitsQZ,ordersQZ,roiQZ,feeQZ");
        sql.append(",showsSS,visitsSS,ordersSS,roiSS,feeSS,collectsSS");
        sql.append(",showsCJ,visitsCJ,ordersCJ,roiCJ,feeCJ,collectsCJ");
        sql.append(",date,remark,dsr");
        sql.append(")");
        sql.append(" VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

        jdbcTemplate.update(sql.toString(),
            entity.getShopId(),entity.getShopType(),entity.getVisits(),entity.getViews(),
            entity.getOrderUsers(),entity.getOrderLKR(),entity.getOrders(),entity.getOrderAmount(),
            entity.getGoods(),entity.getCollects(),
            entity.getCVR(),entity.getATV(),entity.getUVV(),entity.getSH(),entity.getSS(),
            entity.getZBSC(),entity.getZBRJGK(),entity.getZBViews(),entity.getZBOrders(),entity.getZBOrderAmount(),entity.getZBCVR(),
            entity.getFSNew(),entity.getFSOrders(),entity.getFSROrder(),entity.getFSOrderAmount(),entity.getFSFGL(),
         
            entity.getShowsQZ(),entity.getVisitsQZ(),entity.getOrdersQZ(),entity.getRoiQZ(),entity.getFeeQZ(),
            entity.getShowsSS(),entity.getVisitsSS(),entity.getOrdersSS(),entity.getRoiSS(),entity.getFeeSS(),entity.getCollectsSS(),
            entity.getShowsCJ(),entity.getVisitsCJ(),entity.getOrdersCJ(),entity.getRoiCJ(),entity.getFeeCJ(),entity.getCollectsCJ(),
            entity.getDate(),entity.getRemark(),entity.getDsr()
        );

        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ResultVo<Integer> editShopTrafficForPdd(ShopTrafficEntity entity) {
        StringBuilder sql = new StringBuilder("UPDATE dc_shop_traffic SET ");
   
        sql.append("visits=?,views=?,goods=?,collects=?,");
        sql.append("orderUsers=?,orderLKR=?,orders=?,orderAmount=?,");
        sql.append("CVR=?,ATV=?,UVV=?,SH=?,SS=?,ZBSC=?,ZBRJGK=?,ZBViews=?,ZBOrders=?,ZBOrderAmount=?,ZBCVR=?,");
        sql.append("FSNew=?,FSOrders=?,FSROrder=?,FSOrderAmount=?,FSFGL=?");
        sql.append(",showsQZ=?,visitsQZ=?,ordersQZ=?,roiQZ=?,feeQZ=?");
        sql.append(",showsSS=?,visitsSS=?,ordersSS=?,roiSS=?,feeSS=?,collectsSS=?");
        sql.append(",showsCJ=?,visitsCJ=?,ordersCJ=?,roiCJ=?,feeCJ=?,collectsCJ=?");
        sql.append(",date=?,remark=?,dsr=? ");
        sql.append(" WHERE id=? ");

        jdbcTemplate.update(sql.toString(),
   
            entity.getVisits(),entity.getViews(),
            entity.getGoods(),entity.getCollects(),
            entity.getOrderUsers(),entity.getOrderLKR(),entity.getOrders(),entity.getOrderAmount(),
            entity.getCVR(),entity.getATV(),entity.getUVV(),
            entity.getSH(),entity.getSS(),entity.getZBSC(),entity.getZBRJGK(),entity.getZBViews(),entity.getZBOrders(),entity.getZBOrderAmount(),entity.getZBCVR(),
            entity.getFSNew(),entity.getFSOrders(),entity.getFSROrder(),entity.getFSOrderAmount(),entity.getFSFGL(),
            entity.getShowsQZ(),entity.getVisitsQZ(),entity.getOrdersQZ(),entity.getRoiQZ(),entity.getFeeQZ(),
            entity.getShowsSS(),entity.getVisitsSS(),entity.getOrdersSS(),entity.getRoiSS(),entity.getFeeSS(),entity.getCollectsSS(),
            entity.getShowsCJ(),entity.getVisitsCJ(),entity.getOrdersCJ(),entity.getRoiCJ(),entity.getFeeCJ(),entity.getCollectsCJ(),
            entity.getDate(),entity.getRemark(),entity.getDsr(),
            entity.getId()
        );
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ResultVo<Integer> addShopTrafficForDy(ShopTrafficEntity entity) {
        StringBuilder sql = new StringBuilder("INSERT INTO dc_shop_traffic ");
        sql.append("(");
        sql.append("shopId,shopType,shows,paidShows,visits,visitsFs,views,CTR,");
        sql.append("orderUsers,orderNewUsers,orderAmount,CVR,ATV,UVV,");
        sql.append("date,remark,source,sourceName");
        sql.append(")");
        sql.append(" VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

        jdbcTemplate.update(sql.toString(),
            entity.getShopId(),entity.getShopType(),
            entity.getShows(),0,entity.getVisits(),entity.getVisitsFs(),entity.getViews(),entity.getCTR(),
            entity.getOrderUsers(),entity.getOrderNewUsers(),entity.getOrderAmount(),
            entity.getCVR(),entity.getATV(),entity.getUVV(),
            entity.getDate(),entity.getRemark(),entity.getSource(),entity.getSourceName()
        );

        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ShopTrafficEntity getById(Long id) {
     try {
        return jdbcTemplate.queryForObject("SELECT * FROM dc_shop_traffic WHERE id=?", new BeanPropertyRowMapper<>(ShopTrafficEntity.class),id);
        
     } catch (Exception e) {
        return null;
     } 
        
    }


    public String getLastDay(int shopType,Integer shopId) {
        String sql = "SELECT `date` FROM dc_shop_traffic_goods WHERE shopType=? ";
        List<Object> params = new ArrayList<>();
        params.add(shopType);
        if(shopId != null){
            sql+= " AND shopId = ? ";
            params.add(shopId);
        }
        sql +=   "ORDER BY id DESC LIMIT 1 ";

        return jdbcTemplate.queryForObject(sql,String.class ,params.toArray(new Object[params.size()]));
    }


    public ResultVo<Integer> addShopGoodsTrafficForPdd(ShopTrafficGoodsEntity entity){
        //查询店铺
        var shopType = jdbcTemplate.queryForObject("SELECT `type` FROM dc_shop WHERE id=?",Integer.class,entity.getShopId());
        String goodsNumber = "";

        //查询商品信息
        try {
            goodsNumber = jdbcTemplate.queryForObject("SELECT goodsNum from dc_shop_goods where goodsId=?",String.class, entity.getGoodsId());
        } catch (Exception e) {
        }

        entity.setPaidShows(entity.getPaidShowCJ()+entity.getPaidShowSS()+entity.getPaidShowQZ());
        entity.setPaidVisits(entity.getPaidVisitCJ()+entity.getPaidVisitSS()+entity.getPaidVisitQZ());
        entity.setPaidOrders(entity.getPaidOrderCJ()+entity.getPaidOrderSS()+entity.getPaidOrderQZ());

        String sql = "INSERT INTO dc_shop_traffic_goods (shopId,shopType,goodsId,goodsNumber,shows,paidShows,visits,paidVisits,views,collects,"+
                    "paidCollects,orders,paidOrders,carts,paidCarts,chats,paidChats,date,remark,orderUsers,orderAmount,CVR,cvrTag"+
                    ",paidShowCJ,paidVisitCJ,paidOrderCJ,paidShowSS,paidVisitSS,paidOrderSS,paidShowQZ,paidVisitQZ,paidOrderQZ)"+
                    " value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,entity.getShopId(),entity.getShopType(),entity.getGoodsId(),goodsNumber,
        entity.getShows(),entity.getPaidShows(),entity.getVisits(),entity.getPaidVisits(),entity.getViews(),
        entity.getCollects(),entity.getPaidCollects(),entity.getOrders(),entity.getPaidOrders(),entity.getCarts(),entity.getPaidCarts(),
        entity.getChats(),entity.getPaidChats(),entity.getDate(),entity.getRemark(),entity.getOrderUsers(),entity.getOrderAmount(),entity.getCVR(),entity.getCvrTag()
        ,entity.getPaidShowCJ(),entity.getPaidVisitCJ(),entity.getPaidOrderCJ()
        ,entity.getPaidShowSS(),entity.getPaidVisitSS(),entity.getPaidOrderSS()
        ,entity.getPaidShowQZ(),entity.getPaidVisitQZ(),entity.getPaidOrderQZ()
        );
        
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

}
